from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import backref, mapper, relation, sessionmaker
from spass.core.db.spassobj import *
from spass.core.error import error
from spass.core.db.db_core import (core_add_user,
                     core_add_user_to_team,
                     core_add_team_to_teamset,
                     core_new_template)
import datetime, env
from spass import settings
import os

def db_connect(creation=False, verbose=False):
    if settings.DATABASE_ENGINE == 'sqlite3':
        if os.path.exists(settings.DATABASE_NAME) and creation:
            error('already.exists', ["SQLite database", ''])
            return 1
        env.engine = create_engine('sqlite:///'+settings.DATABASE_NAME, echo=verbose)
    else:
        switch = {
            'mysql' : 'mysql',
            'postgres' : 'postgresql',
            'oracle' : 'oracle'
            }
        dialect = switch.get(settings.DATABASE_ENGINE)
        if dialect == None:
            print "Bad Engine settings in \'settings.py\'"
            return 1
            
        env.engine = create_engine(dialect
                                   +settings.DATABASE_USER+':'
                                   +settings.DATABASE_PASSWORD+'@'
                                   +settings.DATABASE_HOST+'/'
                                   +settings.DATABASE_NAME, echo=verbose)
        
    env.Session = sessionmaker(bind=env.engine)
    env.session = env.Session()
    
def init_db_core(example=False, verbose=False):
    ret = db_connect(verbose=verbose, creation=True)
    if ret == 1:
        return 1
    
    user_table = User.__tablename__
    team_table = Team.__tablename__
    team_set_table = TeamSet.__tablename__
    assignment_table = Assignment.__tablename__
    template_table = Template.__tablename__

    env.metadata = base.metadata
    env.metadata.create_all(env.engine)

    if example:
        core_add_user("Petrelli", "Peter")
        core_add_user("Bennet", "Claire")
        core_add_user("Strauss", "Tracy")
        core_add_user("Parkman", "Matt")
        core_add_user("Nakamura", "Hiro")
        core_add_user("Sullivan", "Samuel")
        core_add_user("Jose", "Maurice")

        core_add_user_to_team(1, "Petrelli", "Peter")
        core_add_user_to_team(1, "Bennet", "Claire")
        core_add_user_to_team(2, "Strauss", "Tracy")
        core_add_user_to_team(2, "Parkman", "Matt")
        core_add_user_to_team(3, "Nakamura", "Hiro")
        core_add_user_to_team(3, "Sullivan", "Samuel")
        core_add_user_to_team(4, "Jose", "Maurice")

        core_add_team_to_teamset(1, 1)
        core_add_team_to_teamset(1, 2)
        core_add_team_to_teamset(2, 3)
        core_add_team_to_teamset(2, 4)

        core_new_template("Mon template", os.getenv("SPASS_ROOT")+'/'+'plugins/tests/exset')
        core_new_template("PDP", os.getenv("SPASS_ROOT")+'/'+'plugins/tests/exset')

        env.session.commit()
